-- 首先创建名为mouse的可登录用户(role)，其密码为password，确保其有创建数据库的权限
-- 在PostgreSQL的bin目录下运行：createuser -d -P -U postgres mouse
-- 其次创建名为mouse的库，其编码为UTF8，owner为mouse
-- 在PostgreSQL的bin目录下运行：createdb -E UTF8 -O mouse -U mouse mouse

drop sequence if exists seq_global_id;
create sequence seq_global_id start with 100;

create or replace function f_set_last_modified() returns trigger as $$
begin
    new.c_last_modified_date := now();
    return new;
end;
$$ language plpgsql;

drop table if exists t_industry;
create table t_industry (
    c_id serial primary key,
    c_name varchar(128) not null,
    c_order int,
    c_parent_id bigint
);

drop table if exists t_position_type;
create table t_position_type (
	c_id serial primary key,
    c_name varchar(128) not null,
    c_order int,
    c_level int,
    c_level1_id bigint,
    c_level2_id bigint,
    c_level3_id bigint
);

-- 用户基础信息表，用于登录操作
drop table if exists t_user;
create table t_user (
    c_id serial primary key,
    c_login_name varchar(64) not null unique,
    c_name varchar(64) not null,        -- Nickname
    c_password varchar(255) not null,
    c_salt varchar(64) not null,
    c_roles varchar(255) not null,
    c_register_date timestamp not null
);

drop table if exists t_user_hr;
create table t_user_hr (
    c_id serial primary key,
    c_company_id bigint not null
);

drop table if exists t_user_admin;
create table t_user_admin (
    c_id serial primary key
);

drop table if exists t_user_member;
create table t_user_member (
    c_id serial primary key,
    c_realname varchar(20), -- 真实姓名
    c_gender char(1),
    c_birthday char(10),
    c_headline varchar(255), -- 个人的Headline，由最近一次工作自动生成
    c_working_years int,
    c_cert_type int,
    c_cert_num varchar(30),
    c_residence int,
    c_email varchar(50),
    c_income int,         -- 年薪范围
    c_income_cur int,     -- 币种
    c_situation int,      -- 求职状态
    c_cell varchar(20),
    c_cell_verified boolean,
    c_company_phone varchar(30),
    c_home_phone varchar(30),
    c_hukou int,
    c_keyword varchar(200),
    c_country int,
    c_marriage int,
    c_height int,
    c_political_status int,
    c_zipcode varchar(10),
    c_address varchar(100),
    c_homepage varchar(100),
    c_qq varchar(15),
    c_wangwang varchar(15),
    c_sina_weibo varchar(30),
    c_wechat varchar(30),
    c_profile_completion int not null default 0
);

drop table if exists t_company;
create table t_company (
	c_id serial primary key,
	c_name varchar(128) not null,
	c_status int not null default 0,    -- 0: valid, 1: invalid
	c_balance decimal not null default 0.0,
	c_brief_name varchar(64),
	c_type int not null,
	c_size int not null,
	c_industry int not null,
	c_industry_2 int,
	c_province int not null,
	c_city int not null,
	c_address varchar(256) not null,
	c_zipcode char(6) not null,
	c_homepage varchar(256),
	c_homepage_2 varchar(256),
	c_contactor varchar(20),
	c_cellphone varchar(20),
	c_phone varchar(20),
	c_fax varchar(20),
	c_email varchar(50),
	c_introduction TEXT
);

drop table if exists t_company_department;
create table t_company_department (
	c_id serial primary key,
	c_company_id bigint not null,
	c_name varchar(128) not null,
	c_order int not null
);

drop table if exists t_company_trade;
create table t_company_trade (
	c_id serial primary key,
	c_company_id bigint not null,
	c_datetime timestamp not null,
	c_type int not null,
	c_amount decimal not null
);

drop table if exists t_position;
create table t_position (
	c_id serial primary key,
	c_company_id bigint not null,
	c_department_id bigint not null default 0,
	c_name varchar(128) not null,
	c_code varchar(64),
	c_count int not null default 0,
	c_province int not null,
	c_city int,
	c_age_min int,
	c_age_max int,
	c_position_type int not null,
	c_position_type_2 int,
	c_lang int,
	c_proficiency int,
	c_lang_2 int,
	c_proficiency_2 int,
	c_education int,
	c_working_years int,
	c_major int,
	c_major2 int,
	c_salary_range int not null,
	c_type int not null, -- 0: full time, 1: part time
	c_keywords varchar(256),
	c_description Text,
	c_description_eng Text,
	c_order int not null default 0,
	c_publish_date date not null,
	c_refresh_date date not null,
	c_close_date date not null,
	c_status int not null default 0, -- 0: not published, 1: published
	c_quota decimal not null default 0.0,
	c_last_modified_date timestamp,
	c_promotion_homepage int not null default 0
);
drop trigger if exists tr_position_set_last_modified on t_position;
create trigger tr_position_set_last_modified
before insert or update
on t_position
for each row
execute procedure f_set_last_modified();

drop table if exists t_position_history;
create table t_position_history (
	c_id serial primary key,
	c_position_id bigint not null,
	c_record_date date not null,
	c_hr_user_id bigint not null,
	c_company_id bigint not null,
	c_department_id bigint not null default 0,
	c_name varchar(128) not null,
	c_code varchar(64),
	c_count int not null default 0,
	c_province int not null,
	c_city int,
	c_age_min int,
	c_age_max int,
	c_position_type int not null,
	c_position_type_2 int,
	c_lang int,
	c_proficiency int,
	c_lang_2 int,
	c_proficiency_2 int,
	c_education int,
	c_working_years int,
	c_major int,
	c_major2 int,
	c_salary_range int not null,
	c_type int not null, -- 0: full time, 1: part time
	c_keywords varchar(256),
	c_description Text,
	c_description_eng Text,
	c_order int not null default 0,
	c_publish_date date not null,
	c_refresh_date date not null,
	c_close_date date not null,
	c_status int not null default 0, -- 0: enabled, 1: disable, 10: deleted
	c_quota decimal not null default 0.0
);

drop table if exists t_cv;
create table t_cv (
	c_id serial primary key,
	c_member_id bigint not null,
	c_name varchar(50) not null,
	c_status int not null default 0 -- 0: enabled, 1: disable, 10: deleted
);

drop table if exists t_cv_lang;
create table t_cv_lang (
	c_id serial primary key,
	c_cv_id bigint not null,
	c_lang int,
	c_job_type int,
	c_location int,
	c_industry int,
	c_position_type int,
	c_salary_range int,
	c_onboard int,
	c_self_comment text,
	c_english_level int not null,
	c_japanese_level int,
	c_toefl int,
	c_gre int,
	c_gmat int,
	c_ielts decimal,
	c_toeic decimal   -- TODO: decimal or int
);

drop table if exists t_cv_education;
create table t_cv_education (
	c_id serial primary key,
	c_cv_lang_id bigint not null,
	c_order int not null,
	c_start_date int not null,
	c_end_date int not null default 0,
	c_school varchar(50) not null,
	c_major varchar(50) not null,
	c_degree int not null,
	c_major_description TEXT,
	c_is_oversea char not null default 'n'
);

drop table if exists t_cv_working_experience;
create table t_cv_working_experience (
    c_id serial primary key,
    c_cv_lang_id bigint not null,
    c_order int not null,
    c_start_date int not null,
    c_end_date int not null default 0,
    c_industry int not null default 0,
    c_company varchar(50) not null,
    c_company_size int not null default 0,
    c_company_type int not null default 0,
    c_department varchar(50),
    c_position varchar(50),
    c_description TEXT,
    c_report_to varchar(20),
    c_follower_count int,
    c_witness varchar(20),
    c_dimission_reason varchar(50),
    c_performance TEXT,
    c_is_oversea char not null default 'n'
);

drop table if exists t_cv_languag_skill;

drop table if exists t_application;
create table t_application (
    c_position_id bigint references t_position(c_id),
    c_cv_lang_id bigint references t_cv_lang(c_id),
    c_member_user_id bigint references t_user_member(c_id),
    c_create_date date not null,
    primary key (c_position_id, c_cv_lang_id, c_member_user_id)
);

drop table if exists t_bookmark;
create table t_bookmark (
    c_id serial primary key,
    c_position_id bigint,
    c_company_id bigint,
    c_type int not null default 1,
    c_member_user_id bigint references t_user_member(c_id),
    c_create_date date not null
);
